﻿/*
 * ITEM_KIT TABLE
 *
 * ALLOWS THE BATCHING OF AN ITEM_MANUFACTURER ITEMS IN A KIT
 */
CREATE INDEX "ITEM_TRANSACTION_FACILITY_CODE" ON "ITEM_TRANSACTION"("HEALTH_FACILITY_CODE");
CREATE INDEX "GTIN_HF_STOCK_POLICY_FACILITY_CODE" ON "GTIN_HF_STOCK_POLICY"("HEALTH_FACILITY_CODE");
CREATE TABLE "ITEM_KIT" (
	"PARENT_GTIN"		TEXT NOT NULL, -- THE GTIN OF THE KIT IF APPLICABLE OTHERWISE A CORRELATION STRING TO LINK MULTIPLE KIT TUPLES TOGETHER
	"CHILD_GTIN"		TEXT NOT NULL, -- SHOULD BE VARCHAR BUT THE PKS HAVE TO MATCH
	"QTY_CHILD_PARENT_UOM" 	INTEGER, -- CHILD PER UOM
	"NOTES"			TEXT, -- NOTES
	"MODIFIED_ON"		TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- THE TIME THE KIT WAS MODIFIED
	"MODIFIED_BY"		INTEGER NOT NULL,
	CONSTRAINT "PK_ITEM_KIT" PRIMARY KEY ("PARENT_GTIN","CHILD_GTIN"),
	CONSTRAINT "FK_ITEM_KIT_CHILD_ITEM_MANUFACTURER" FOREIGN KEY ("CHILD_GTIN") REFERENCES "ITEM_MANUFACTURER"("GTIN"),
	CONSTRAINT "FK_ITEM_KIT_PARENT_ITEM_MANUFACTURER" FOREIGN KEY ("PARENT_GTIN") REFERENCES "ITEM_MANUFACTURER"("GTIN"),
	CONSTRAINT "FK_ITEM_KIT_MODIFIED_BY" FOREIGN KEY ("MODIFIED_BY") REFERENCES "USER"("ID")
);
CREATE INDEX "ITEM_KIT_KIT_GTIN_IDX" ON "ITEM_KIT"("PARENT_GTIN");
CREATE INDEX "ITEM_KIT_ITEM_GTIN_IDX" ON "ITEM_KIT"("CHILD_GTIN");

-- todo: insert some kits here and bundle syringes
INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('3809090901','3809090902',1,'BCG KIT - DILUENT',1);
INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('3809090901','SR-304950',1,'BCG KIT - ADS 0.5M',1);

INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('3908080801','3908080802',1,'MEASLES - DILUENT',1);
INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('3908080801','SR-304950',1,'MEASLES - ADS 0.5M',1);


INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('1234567889771','1234567890302',1,'BCG - DILUENT',1);
INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('1234567889771','SR-304950',1,'BCG - ADS 0.5M',1);

INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('1234567889757','1234567890142',1,'MEASLES - DILUENT',1);
INSERT INTO "ITEM_KIT" ("PARENT_GTIN","CHILD_GTIN","QTY_CHILD_PARENT_UOM","NOTES","MODIFIED_BY") VALUES 
	('1234567889757','SR-304950',1,'MEASLES - ADS 0.5M',1);

/* 
 * PER-FACILITY ORDER CREATION ROUTINE
 *
 * CALCULATES THE AVERAGE CONSUMPTION OF A PARTICULAR ITEM AND CONSTRUCTS A TRANSFER ORDER HAVING THE CORRECT
 * QTY TO BRING THE FACILITY BACK TO MAXIMUM STOCK LEVEL
 */


 -- GET REPLENISHMENT ORDER
 DROP FUNCTION IF EXISTS GET_REPLENISHMENT_ORDER
(
	IN VARCHAR
);
CREATE OR REPLACE FUNCTION GET_REPLENISHMENT_ORDER
(
	HF_ID_IN IN VARCHAR
)
RETURNS TABLE (
	GTIN TEXT,
	HEALTH_FACILITY_CODE TEXT,
	SAFETY_STOCK REAL,
	BALANCE_OH REAL,
	AMC_CALC FLOAT,
	NEEDED_REPLENISH_QTY FLOAT,
	NAME TEXT,
	ORDER_PKG_QTY FLOAT,
	ORDER_PKG_UOM TEXT,
	BASE_REPLENISH_QTY FLOAT,
	BASE_UOM TEXT
)
AS $$
BEGIN
	RETURN QUERY WITH RECURSIVE GTIN_KIT("GTIN") AS 
	(
		-- THIS RETRIEVES THE GTIN POLICY FOR A HF AS WELL AS THE AMC
		WITH GTIN_AMC AS (
			SELECT 
				"HEALTH_FACILITY_CODE",
				"GTIN",
				CEIL(SUM(CONSUMPTION)/COUNT("DATE")) AS AMC --(DATE_PART('month',(AGE(MAX("DATE"), MIN("DATE")))) + 1)) AS AMC
			FROM FACILITY_MONTHLY_CONSUMPTION 
			WHERE 
				"HEALTH_FACILITY_CODE" = HF_ID_IN
			GROUP BY "HEALTH_FACILITY_CODE", "GTIN"
		) 
		-- THIS QUERY USES THE KIT TABLE TO DETERMINE WHAT THE DEMAND 'SHOULD BE' FOR DEPENDENT ITEMS
		SELECT 
			"GTIN", 
			"HEALTH_FACILITY_CODE",
			AMC,
			FALSE AS IS_KIT_ITEM
		FROM GTIN_AMC
		UNION ALL
		SELECT 
			"CHILD_GTIN", 
			GTIN_KIT."HEALTH_FACILITY_CODE",
			AMC * "ITEM_KIT"."QTY_CHILD_PARENT_UOM",
			TRUE AS IS_KIT_ITEM
		FROM "ITEM_KIT"
			INNER JOIN GTIN_KIT ON ("PARENT_GTIN" = GTIN_KIT."GTIN")
	),
	-- AGGREGATE THE AMC OF THE KITTED ITEMS SO THEY'RE UNIQUE
	 GTIN_AMC_KIT_AGGREGATE AS (
		SELECT 
			"GTIN", 
			"HEALTH_FACILITY_CODE", 
			SUM(CASE WHEN IS_KIT_ITEM THEN AMC ELSE 0 END) AS KIT_AMC,
			SUM(CASE WHEN IS_KIT_ITEM THEN 0 ELSE AMC END) AS ACTUAL_AMC
		FROM GTIN_KIT 
		GROUP BY "GTIN", "HEALTH_FACILITY_CODE"
	),
	-- PICK THE GREATER OF ACTUAL OR KITTED
	GTIN_AMC_AGGREGATE AS (
		SELECT 
			"GTIN", 
			"HEALTH_FACILITY_CODE",
			CASE WHEN KIT_AMC > ACTUAL_AMC THEN KIT_AMC ELSE ACTUAL_AMC END AS AMC
		FROM GTIN_AMC_KIT_AGGREGATE
	),
	-- THE REPLENISHMENT REQUIREMENTS THAT IS AMC + SAFETY_STOCK - BALANCE OF THE ITEM TO GET THE NEEDED REPLENISHMENT 
	 REPLENISH_REQ AS (
		SELECT "GTIN", "HEALTH_FACILITY_CODE", COALESCE("SAFETY_STOCK",0) AS SAFETY_STOCK, COALESCE("BALANCE", 0) BALANCE_OH, AMC, AMC + COALESCE("SAFETY_STOCK",0) - COALESCE("BALANCE",0) AS NEEDED_REPLENISH  FROM GTIN_AMC_AGGREGATE
			LEFT JOIN "HEALTH_FACILITY_BALANCE" USING ("GTIN", "HEALTH_FACILITY_CODE")
			LEFT JOIN "GTIN_HF_STOCK_POLICY" USING ("GTIN", "HEALTH_FACILITY_CODE")
	), ACTUAL_REPLENISH AS (
		SELECT REPLENISH_REQ.*, "ITEM"."NAME", CEIL(NEEDED_REPLENISH / "ALT_1_QTY_PER") AS ALT_1_QTY, "ALT_1_UOM", CEIL(NEEDED_REPLENISH / "ALT_1_QTY_PER") * "ALT_1_QTY_PER" AS BASE_REPLENISH, "BASE_UOM" FROM REPLENISH_REQ
				INNER JOIN "ITEM_MANUFACTURER" USING ("GTIN")
				INNER JOIN "ITEM" ON ("ITEM_ID" = "ITEM"."ID")
			WHERE "ITEM_MANUFACTURER"."IS_ACTIVE" = TRUE
	)
	SELECT * FROM ACTUAL_REPLENISH WHERE BASE_REPLENISH > 0;
END $$ LANGUAGE plpgsql;
UPDATE "WORD_TRANSLATE" SET "NAME" = 'Linked Items' WHERE "CODE" = 'ItemManufacturerGTINParent';
INSERT INTO "ADJUSTMENT_REASON" ("NAME","IS_ACTIVE","NOTES","MODIFIED_ON","MODIFIED_BY", "POSITIVE")
	VALUES ('Receipt',TRUE,'Receipt',CURRENT_TIMESTAMP,1, TRUE);


INSERT INTO "ACTIONS" ("NAME") VALUES ('CreateOrderFromDrp');
INSERT INTO "ACTIONS" ("NAME") VALUES ('RivoReceipts');
INSERT INTO "ACTIONS" ("NAME") VALUES ('UploadWeightTallySheet');

INSERT INTO "ROLE_ACTION" ("ROLE_ID", "ACTION_ID") VALUES (1, (SELECT "ID" FROM "ACTIONS" WHERE "NAME" = 'CreateOrderFromDrp'));
INSERT INTO "ROLE_ACTION" ("ROLE_ID", "ACTION_ID") VALUES (1, (SELECT "ID" FROM "ACTIONS" WHERE "NAME" = 'RivoReceipts'));
INSERT INTO "ROLE_ACTION" ("ROLE_ID", "ACTION_ID") VALUES (1, (SELECT "ID" FROM "ACTIONS" WHERE "NAME" = 'UploadWeightTallySheet'));
INSERT INTO "ROLE_ACTION" ("ROLE_ID", "ACTION_ID") VALUES (4, (SELECT "ID" FROM "ACTIONS" WHERE "NAME" = 'CreateOrderFromDrp'));
INSERT INTO "ROLE_ACTION" ("ROLE_ID", "ACTION_ID") VALUES (4, (SELECT "ID" FROM "ACTIONS" WHERE "NAME" = 'UploadWeightTallySheet'));
